package Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Pojo.User;
import Tools.LoginTools;

public class UserDaoImpl implements UserDao{



	@Override
	public List<User> selectAll() {
		List<User> list = new ArrayList<>();
		Connection conn = LoginTools.getConnection();
		String sql = "select * from users where flag = 1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPassword(rs.getString("password"));
				u.setAge(rs.getInt("age"));
				u.setSex(rs.getString("sex"));
				u.setPhone(rs.getString("phone"));
				u.setQQ(rs.getString("QQ"));
				u.setWeixin(rs.getString("weixin"));
				u.setAddress(rs.getString("address"));
				u.setEmail(rs.getString("email"));
				list.add(u);
				u = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			LoginTools.closeResource(conn, ps, rs);
		}
		return list;
	}

	
	
	public User selectByName(String username) {
		Connection conn = LoginTools.getConnection();
		String sql = "select * from users where username = ?";
		PreparedStatement ps = null;
		ResultSet rs= null;
		System.out.println("Dao - username: " + username);
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			if(rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPassword(rs.getString("password"));
				u.setAge(rs.getInt("age"));
				u.setSex(rs.getString("sex"));
				u.setPhone(rs.getString("phone"));
				u.setQQ(rs.getString("QQ"));
				u.setWeixin(rs.getString("weixin"));
				u.setAddress(rs.getString("address"));
				u.setEmail(rs.getString("email"));
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}



	@Override
	public int addUser(String username, String password, int age, String sex,String remake, String phone, String QQ, String weixin, String email, String address) {
		Connection conn = LoginTools.getConnection();
		String sql = "INSERT INTO `users` (`username`, `password`, `age`, `sex`, `remake`, `flag`) VALUES (?, ?, ?, ?, ?, ?)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setInt(3, age);
			ps.setString(4, sex);
			ps.setString(5, phone);
			ps.setString(6, QQ);
			ps.setString(7, weixin);
			ps.setString(8, email);
			ps.setString(9, address);
			ps.setString(10, remake);
			ps.setInt(6, 1);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			LoginTools.closeResource(conn, ps, null);
		}
		return n;
	}



	@Override
	public int updateUser(int id, String username, String password, int age, String gender,String remake, String sex, String phone, String QQ, String weixin, String email, String address) {
		Connection conn = LoginTools.getConnection();
		String sql = "UPDATE `users` SET `username`=?, `password`=?,"
				+ " `age`=?, `sex`=?, `remake`=? WHERE (`id`=?)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setInt(3, age);
			ps.setString(4, sex);
			ps.setString(5, phone);
			ps.setString(6, QQ);
			ps.setString(7, weixin);
			ps.setString(8, email);
			ps.setString(9, address);
			ps.setString(10, remake);
			ps.setInt(11, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			LoginTools.closeResource(conn, ps, null);
		}
		return n;
	
	}



	@Override
	public int deleteUser(int id) {
		Connection conn = LoginTools.getConnection();
		String sql = "update users set flag=0 where id=?";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			LoginTools.closeResource(conn, ps, null);
		}
		return n;
	}



	@Override
	public User selectById(int id) {
		Connection conn = LoginTools.getConnection();
		String sql = "select * from users where id = ?";
		PreparedStatement ps = null;
		ResultSet rs= null;
		System.out.println("Dao - username: " + id);
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPassword(rs.getString("password"));
				u.setAge(rs.getInt("age"));
				u.setSex(rs.getString("sex"));
				u.setPhone(rs.getString("phone"));
				u.setQQ(rs.getString("QQ"));
				u.setWeixin(rs.getString("weixin"));
				u.setAddress(rs.getString("address"));
				u.setEmail(rs.getString("email"));
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	
	}
	
	//找回密码
	public int findUser(int id, String username, ResultSet rs) {
		Connection conn = LoginTools.getConnection();
		String sql = "select * from users where username=? and password=?";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPassword(rs.getString("password"));
				u.setAge(rs.getInt("age"));
				u.setSex(rs.getString("sex"));
				u.setPhone(rs.getString("phone"));
				u.setQQ(rs.getString("QQ"));
				u.setWeixin(rs.getString("weixin"));
				u.setAddress(rs.getString("address"));
				u.setEmail(rs.getString("email"));
				u = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			LoginTools.closeResource(conn, ps, rs);
		}
		return n;
	}
	
	//以密码和数据库自动更新的编号id为参数修改会员的密码
	@SuppressWarnings({ "null", "unused" })
	public boolean updatePassword(String password, Integer id) {
		Connection conn = LoginTools.getConnection();
		String sql = "update users set password=? where id=?";
		PreparedStatement ps = null;
		try {
			ps.setString(1, password);
			ps.setInt(2, id.intValue());
			ps.executeUpdate();
			ps.close();
			return true;
		}
		catch (SQLException ex) {
			return false;
		}
	}
	
	@Override
	public int updateUser(int id, String username, String password, int age, String sex, int n) {
		// TODO Auto-generated method stub
		return n;
	}



	@Override
	public int addUser(String username, String password, int age, String sex) {
		// TODO Auto-generated method stub
		return age;
	}



	@Override
	public int updateUser(int id, String username, String password, int age, String sex) {
		// TODO Auto-generated method stub
		return age;
	}



	@Override
	public int addUser(String username, String password, int age, String sex, String remake) {
		// TODO Auto-generated method stub
		return 0;
	}



	@Override
	public int updateUser(int i, String username, String password, int a, String sex, String remake) {
		// TODO Auto-generated method stub
		return 0;
	}

}
